package com.zhenzhang.myblog.dao;

import com.zhenzhang.myblog.entity.Article;
import org.apache.ibatis.annotations.*;

import javax.print.DocFlavor;
import java.util.*;

public interface ArticleDao {
    @Select("SELECT article.id, article.title, article.likes," +
            " article.created_at, article.updated_at, article.content, article.cover, user.username" +
            " FROM article LEFT JOIN user ON article.aid = user.id " +
            "WHERE article.id = #{id} ")
    Article getArticleContentById(Integer id);

    @Select("SELECT id FROM article WHERE title = #{title} AND aid = #{aid}")
    int getArticleIdByTitleAndAid(String title, Integer aid);

    @Select("SELECT EXISTS(SELECT id FROM article WHERE title = #{title} AND aid = #{aid} AND id != #{id})")
    int isArticleTitleExists(String title, Integer aid, Integer id);

    @Select("SELECT EXISTS(SELECT 1 FROM article WHERE id = #{id})")
    int isArticleExists(Integer id);

    /**
     * 获取指定作者的文章列表
     * @param aid   作者ID
     * @param size  每页大小
     * @param offset 偏移量
     * @return  文章列表
     */
    @Select("SELECT article.aid, article.id, article.title, article.likes," +
            " article.abstract AS abstract0, article.created_at, article.updated_at, article.cover, article.editing, user.username FROM article " +
            "LEFT JOIN user ON article.aid = user.id" +
            " WHERE aid = #{aid} AND article.editing = 0 " +
            "LIMIT #{offset}, #{size}"
    )
    List<Article> getArticleListByAid(@Param("aid") Integer aid,
                                      @Param("size") Integer size,
                                      @Param("offset") Integer offset);

    /**
     * 根据关键字获取指定作者的文章列表
     * @param aid       作者ID
     * @param keyWord   关键字
     * @param size      每页大小
     * @param offset    偏移量
     * @return  搜索结果
     */
    @Select("SELECT article.aid, article.id, article.title, article.likes," +
            " article.abstract AS abstract0, article.created_at, article.updated_at, article.cover, article.editing, user.username FROM article " +
            "LEFT JOIN user ON article.aid = user.id" +
            " WHERE (title LIKE CONCAT('%',#{keyWord},'%') OR content LIKE CONCAT('%',#{keyWord},'%')) AND aid = #{aid} AND article.editing = 0 "+
            "LIMIT #{offset}, #{size}")
    List<Article> getArticleListByKeyWordAndAid(@Param("aid") Integer aid,
                                                @Param("keyWord") String keyWord,
                                                @Param("size") Integer size,
                                                @Param("offset") Integer offset);

    /**
     * 根据关键字获取全站文章中符合约束的文章列表
     * @param keyWord   关键字
     * @param size      每页大小
     * @param offset    偏移量
     * @return  搜索结果
     */
    @Select("SELECT article.aid, article.id, article.title, article.likes," +
            " article.abstract AS abstract0, article.created_at, article.updated_at, article.cover, article.editing, user.username FROM article " +
            "LEFT JOIN user ON article.aid = user.id" +
            " WHERE (title LIKE CONCAT('%',#{keyWord},'%') OR content LIKE CONCAT('%',#{keyWord},'%')) AND article.editing = 0 "+
            "LIMIT #{offset}, #{size}")
    List<Article> getArticleListByKeyWord(@Param("keyWord") String keyWord,
                                          @Param("size") Integer size,
                                          @Param("offset") Integer offset);

    /**
     * 获取正在编辑中的文章列表
     * @param aid   作者id
     * @param size  每页大小
     * @param offset    偏移量
     * @return  文章列表
     */
    @Select("SELECT article.aid, article.id, article.title, article.likes," +
            " article.abstract AS abstract0, article.created_at, article.updated_at, article.cover, article.editing, user.username FROM article " +
            "LEFT JOIN user ON article.aid = user.id" +
            " WHERE aid = #{aid} AND article.editing = 1 " +
            "LIMIT #{offset}, #{size}")
    List<Article> getEditingArticleListByAid(@Param("aid") Integer aid,
                                             @Param("size") Integer size,
                                             @Param("offset") Integer offset);

    /**
     * 获取正在编辑中的文章数量
     * @param aid   作者id
     * @return  文章数
     */
    @Select("SELECT COUNT(*) FROM article WHERE aid = #{aid} AND editing = 1")
    int getEditingArticleListCountByAid(Integer aid);

    /**
     * 获取作者文章总数
     * @param aid   作者ID
     * @return 文章数
     */
    @Select("SELECT COUNT(*) FROM article WHERE aid = #{aid} AND article.editing = 0 ")
    int getArticleListCountByAid(Integer aid);

    /**
     * 根据关键字搜索特定用户文章的结果总数
     * @param aid       作者ID
     * @param keyWord   关键字
     * @return  结果数
     */
    @Select("SELECT COUNT(*) FROM article" +
            " WHERE (title LIKE CONCAT('%',#{keyWord},'%') OR content LIKE CONCAT('%',#{keyWord},'%')) AND aid = #{aid} AND article.editing = 0 ")
    int getArticleListCountByAidAndKeyWord(Integer aid, String keyWord);

    /**
     * 根据关键字搜索所有文章的结果的总数
     * @param keyWord   关键字
     * @return  结果数
     */
    @Select("SELECT COUNT(*) FROM article" +
            " WHERE (title LIKE CONCAT('%',#{keyWord},'%') OR content LIKE CONCAT('%',#{keyWord},'%')) AND editing = 0")
    int getAllArticleListCountByKeyWord(String keyWord);

    /**
     * 获取所有的文章列表
     * @param size  每页大小
     * @param offset 偏移量
     * @return  文章列表
     */
    @Select("SELECT article.aid, article.id, article.title, article.likes," +
            " article.abstract AS abstract0, article.created_at, article.updated_at, article.cover, article.editing, user.username" +
            " FROM article LEFT JOIN user ON article.aid = user.id WHERE article.editing = 0 " +
            "LIMIT #{offset}, #{size}"
    )
    List<Article> getAllArticleList(@Param("size") Integer size,
                                      @Param("offset") Integer offset);

    /**
     * 获取所有文章的总数
     * @return 文章数
     */
    @Select("SELECT COUNT(*) FROM article WHERE editing = 0")
    int getAllArticleListCount();

    @Select("SELECT * FROM article WHERE id = #{id}")
    int getArticleById(Integer id);

    @Select("SELECT type.type FROM article_type " +
            "LEFT JOIN type ON type.id = article_type.type_id " +
            "GROUP BY type_id " +
            "ORDER BY COUNT(*) DESC")
    List<String> getDescTypeList();

    /**
     * 添加文章
     * @param article 文章对象
     */
    @Insert("INSERT INTO article " +
            "(`id`, `aid`, `title`, `content`, `abstract`, `created_at`, `updated_at`, `editing`, `cover`) " +
            "VALUES " +
            "(#{id}, #{aid}, #{title}, #{content}, #{abstract0}, NOW(), NOW(), #{editing}, #{cover})")
    void addArticle(Article article);

    // 添加映射
    @Insert("INSERT INTO article_type " +
            "(`article_id`, `type_id`) " +
            "VALUES " +
            "(#{article_id}, #{type_id})")
    void addArticleType(Integer article_id, Integer type_id);

    // 添加type
    @Insert("INSERT INTO type (`type`) VALUES (#{type})")
    void addType(String type);

    //查看type是否存在
    @Select("SELECT EXISTS(SELECT type FROM type WHERE type = #{type})")
    int TypeExist(String type);

    //查看某一具体映射是否存在
    @Select("SELECT EXISTS(SELECT * FROM article_type WHERE article_id = #{article_id} AND type_id = #{type_id})")
    int typeMapperExist(Integer article_id, Integer type_id);

    // 查看映射中是否存在某一类型
    @Select("SELECT EXISTS(SELECT type_id FROM article_type WHERE type_id = #{type_id})")
    int articleTypeExist(Integer type_id);

    // 获取type的ID
    @Select("SELECT id FROM type WHERE type = #{type}")
    int getTypeId(String type);

    /**
     * 根据文章id获取文章标签
     * @param id 文章id
     * @return  文章标签列表
     */
    @Select("SELECT type FROM type " +
            "INNER JOIN article_type ON article_type.type_id = type.id " +
            "INNER JOIN article ON article.id=#{id} AND article.id = article_type.article_id")
    List<String> getTypeById(Integer id);

    /**
     * 根据类型名称获取文章列表
     * @param type  类型名称
     * @return  文章列表
     */
    @Select("SELECT article.aid, article.id, article.title, article.likes, article.abstract AS abstract0, " +
            "article.created_at, article.updated_at, article.cover, user.username FROM article " +
            "LEFT JOIN user ON article.aid = user.id " +
            "INNER JOIN article_type ON article_type.article_id = article.id " +
            "INNER JOIN type ON type.type = #{type} AND article_type.type_id = type.id AND article.editing = 0 " +
            "LIMIT #{offset}, #{size}")
    List<Article> getArticleByType(@Param("type") String type,
                                   @Param("size") Integer size,
                                   @Param("offset") Integer offset);

    // 根据类型获取文章列表的总数
    @Select("SELECT COUNT(*) FROM article " +
            "INNER JOIN article_type ON article_type.article_id = article.id " +
            "INNER JOIN type ON type.type = #{type} AND article_type.type_id = type.id AND article.editing = 0")
    int getArticleListCountByType(String type);

    @Select("SELECT article.aid, article.id, article.title, article.likes, article.abstract AS abstract0 " +
            "article.created_at, article.updated_at, article.cover, user.username FROM article " +
            "LEFT JOIN user ON article.aid = user.id " +
            "INNER JOIN article_type ON article_type.article_id = article.id " +
            "INNER JOIN type ON type.type = #{type} AND article_type.type_id = type.id AND article.aid = #{aid} AND article.editing = 0 " +
            "LIMIT #{offset}, #{size}")
    List<Article> getArticleByTypeAndAid(@Param("type") String type,
                                         @Param("aid") Integer aid,
                                         @Param("size") Integer size,
                                         @Param("offset") Integer offset);

    // 根据文章类型和作者id获取文章列表的总数
    @Select("SELECT COUNT(*) FROM article " +
            "INNER JOIN article_type ON article_type.article_id = article.id " +
            "INNER JOIN type ON type.type = #{type} AND article_type.type_id = type.id AND article.aid = #{aid} AND article.editing = 0 ")
    int getArticleListCountByTypeAndAid(String type, Integer aid);

    @Delete("DELETE FROM type WHERE type = #{type}")
    void deleteType(String type);

    @Delete("DELETE FROM article_type WHERE article_id = #{article_id} AND type_id = #{type_id}")
    void deleteArticleType(Integer article_id, Integer type_id);

    @Delete("DELETE FROM article_type WHERE article_id = #{article_id}")
    void deleteAllArticleTypeById(Integer article_id);

    /**
     * 修改文章
     * @param article 文章对象
     */
    @Update("UPDATE article SET " +
            "title= #{title}, content= #{content}, abstract = #{abstract0}, updated_at = NOW(), editing = #{editing} " +
            "WHERE id= #{id}")
    void updateArticle(Article article);

    /**
     * 删除文章
     * @param id 文章id
     */
    @Delete("DELETE FROM article WHERE id = #{id}")
    void deleteArticle(Integer id);

    /**
     * 批量删除文章
     * @param list  存放待删除文章id的数组
     */
    @Delete({"<script> " +
                " DELETE FROM article WHERE id IN " +
                " <foreach collection='list' item='item' separator=',' open='(' close=')'>" +
                    " #{item} " +
                " </foreach> "+
            " </script>"})
    void batchDeleteArticle(ArrayList<Integer> list);

    @Update("UPDATE article SET cover=#{filename} WHERE id = #{id}")
    void setCover(@Param("id") Integer id,@Param("filename") String filename);

    @Select("SELECT cover FROM article WHERE id = #{id}")
    String getArticleCover(Integer id);

    @Select("SELECT MAX(id) FROM article")
    Integer getMaxId();

    @Update("UPDATE article SET likes = (likes + 1) WHERE id = #{id}")
    void thumbsUp(Integer id);

    @Select("SELECT likes FROM article WHERE id = #{id}")
    int getLikes(Integer id);

    @Select("SELECT updated_at FROM article WHERE id = (SELECT MAX(id) FROM article WHERE aid = #{id})")
    Date getLastUpdateTime(Integer id);

    @Select("SELECT SUM(likes) FROM article WHERE aid = #{id}")
    int sumLikes(Integer id);
}
